package dao;

import pojo.ShangHanTiaoBian;
import util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ShangHanTiaoBianDao {
	private Connection conn = null;
	private PreparedStatement pstm = null;
	private ResultSet rs = null;
	//伤寒条辩增加功能
    public void add(ShangHanTiaoBian shtb) {
        conn = DbUtil.getConnection();
        String sql = "insert into ShangHanTiaoBian(num,content,comment) values(?,?,?)";
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,shtb.getNum());
            pstm.setString(2,shtb.getContent());
            pstm.setString(3, shtb.getComment());
            pstm.executeUpdate();
        }catch (SQLException e) {
            e.getStackTrace();
        }finally {
            DbUtil.close(conn,pstm,null);
        }
    }
    //伤寒条辩更新功能
    public void update(ShangHanTiaoBian shtb) {
        try{
            conn = DbUtil.getConnection();
            String sql = "update ShangHanTiaoBian set num=?,content=?,comment=? where id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,shtb.getNum());
            pstm.setString(2,shtb.getContent());
            pstm.setString(3,shtb.getComment());
            pstm.setInt(4,shtb.getId());
            pstm.executeUpdate();
        }catch (Exception e){
            e.getStackTrace();
        }finally {
            DbUtil.close(conn,pstm,null);
        }

    }
	//伤寒条辨展示列表
	public List<ShangHanTiaoBian> getShangHanTiaoBianList(int currentPage,int pageSize) {
		List<ShangHanTiaoBian> shtbList = new ArrayList<>();
		try {
			conn = DbUtil.getConnection();
			String sql;
            sql = "select * from ShangHanTiaoBian limit "+pageSize*(currentPage-1)+","+pageSize;
            pstm = conn.prepareStatement(sql);
			rs = pstm.executeQuery(sql);
			while(rs.next()) {
			        ShangHanTiaoBian shtb = new ShangHanTiaoBian();
			        shtb.setId(rs.getInt("id"));
			        shtb.setNum(rs.getInt("num"));
			        shtb.setContent(rs.getString("content"));
			        shtb.setComment(rs.getString("comment"));
                    shtbList.add(shtb);
			}
            }catch(Exception e) {
                e.printStackTrace();
		}finally {
		    DbUtil.close(conn,pstm,rs);
        }
        System.out.println(shtbList.get(0).toString());
		return shtbList;
	}

    //    关键字查询
    public List<ShangHanTiaoBian> getQueryShtbList(String key,int flag){
        List<ShangHanTiaoBian> shtbList = new ArrayList<>();
        String sql=null;
//        0内容查询
//        1注释查询
        switch (flag){
            case 0:{
                sql="select * from shanghantiaobian where content like '%"+key+"%'";
                break;
            }
            case 1:{
                sql="select * from shanghantiaobian where comment like '%"+key+"%'";
                break;
            }
            default:{
                System.out.println("未知错误");
                break;
            }
        }
        try{
            conn  = DbUtil.getConnection();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery(sql);
            while(rs.next()){
                ShangHanTiaoBian shtb = new ShangHanTiaoBian();
                shtb.setId(rs.getInt("id"));
                shtb.setNum(rs.getInt("num"));
                shtb.setContent(rs.getString("content"));
                shtb.setComment(rs.getString("comment"));
                shtbList.add(shtb);
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return shtbList;
    }

    //伤寒条辩按id查询
    public ShangHanTiaoBian getShtb(int id) {
        ShangHanTiaoBian shtb = null;
        try {
            conn = DbUtil.getConnection();
            String sql = "select * from ShangHanTiaoBian where id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            rs = pstm.executeQuery();
            while(rs.next()) {
                shtb = new ShangHanTiaoBian();
                shtb.setId(rs.getInt("id"));
                shtb.setNum(rs.getInt("num"));
                shtb.setContent(rs.getString("content"));
                shtb.setComment(rs.getString("comment"));
            }

        } catch (Exception e) {
            e.getStackTrace();
        } finally {
            DbUtil.close(conn,pstm,rs);
        }
        return shtb;

    }
    //伤寒条辩删除功能
    public void delete(int id) {
        try {
            conn = DbUtil.getConnection();
            String sql = "delete from ShangHanTiaoBian where id=?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            pstm.executeUpdate();
        }catch(Exception e){
            e.getStackTrace();
        }finally {
            DbUtil.close(conn,pstm,null);
        }
    }
    public ShangHanTiaoBian getDetail(int id) {
        ShangHanTiaoBian shtb = new ShangHanTiaoBian();
        try{
            conn  = DbUtil.getConnection();
            String sql = "select * from shanghantiaobian where id =?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1,id);
            rs = pstm.executeQuery();
            while(rs.next()){
                shtb.setId(rs.getInt("id"));
                shtb.setNum(rs.getInt("num"));
                shtb.setContent(rs.getString("content"));
                shtb.setComment(rs.getString("comment"));
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return shtb;
    }
    public int getTotalNum(){
        int totalNum=0;
        try{
            conn  = DbUtil.getConnection();
            String sql = "select count(*) as totalnum from ShangHanTiaoBian";
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery(sql);
            while(rs.next()){
                totalNum=rs.getInt("totalnum");
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            DbUtil.close(conn, pstm, rs);
        }
        return totalNum;
    }
	}
